package cmpe273.proj1.dao;

import static cmpe273.proj1.dao.DAOUtil.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map.Entry;

import cmpe273.proj1.model.Course;
import cmpe273.proj1.model.Student;

/**
 * This class represents a concrete JDBC implementation of the {@link UserDAO} interface.
 *
 * @author BalusC
 * @link http://balusc.blogspot.com/2008/07/dao-tutorial-data-layer.html
 */
public class StudentDAOJDBC implements StudentDAO {

    // Constants ----------------------------------------------------------------------------------
	
	private static final String SQL_FIND_BY_ID =
			"SELECT s.Student_ID, s.StudentId, s.FirstName, s.LastName, s.Address, s.City, s.State, s.ZipCode, c.Course_ID, c.Name, c.Section, c.MeetingTime, c.Location " +
					"FROM student s " +
					"LEFT JOIN enrolled_in e ON s.Student_ID = e.Student_ID " +
					"LEFT JOIN course c ON c.Course_ID = e.course_ID " +
					"WHERE s.Student_ID = ? " +
					"ORDER BY Student_ID " +
					"LIMIT 1";
	private static final String SQL_FIND_BY_SID =
			"SELECT s.Student_ID, s.StudentId, s.FirstName, s.LastName, s.Address, s.City, s.State, s.ZipCode, c.Course_ID, c.Name, c.Section, c.MeetingTime, c.Location " +
					"FROM student s " +
					"LEFT JOIN enrolled_in e ON s.Student_ID = e.Student_ID " +
					"LEFT JOIN course c ON c.Course_ID = e.course_ID " +
					"WHERE s.StudentId = ? " +
					"ORDER BY Student_ID " +
					"LIMIT 1";
	private static final String SQL_FIND_BY_COURSE =
			"SELECT s.Student_ID, s.StudentId, s.FirstName, s.LastName, s.Address, s.City, s.State, s.ZipCode " +
					"FROM student s, course c, enrolled_in e " +
					"WHERE s.Student_ID = e.Student_ID and c.Course_ID = e.course_ID AND e.course_ID = ? " +
					"ORDER BY Student_ID " +
					"LIMIT 1000"; //a large limit (larger than any one class hopefully)
	private static final String SQL_FIND_ALL =
			"SELECT Student_ID, StudentId, FirstName, LastName, Address, City, State, ZipCode FROM student ORDER BY Student_ID";
	private static final String SQL_FIND_ALL_WITH_COURSES =
			"SELECT s.Student_ID, s.StudentId, s.FirstName, s.LastName, s.Address, s.City, s.State, s.ZipCode, c.Course_ID, c.Name, c.Section, c.MeetingTime, c.Location " +
					"FROM student s " + 
					"LEFT JOIN enrolled_in e ON s.Student_ID = e.Student_ID " +
					"LEFT JOIN course c ON c.Course_ID = e.course_ID " +
					"ORDER BY Student_ID";
	private static final String SQL_FIND_BY_GENERIC =
			"SELECT s.Student_ID, s.StudentId, s.FirstName, s.LastName, s.Address, s.City, s.State, s.ZipCode FROM student s";
	private static final String SQL_FIND_BY_GENERIC_WITH_COURSES =
			"SELECT s.Student_ID, s.StudentId, s.FirstName, s.LastName, s.Address, s.City, s.State, s.ZipCode, c.Course_ID, c.Name, c.Section, c.MeetingTime, c.Location " +
					"FROM student s " +
					"LEFT JOIN enrolled_in e ON s.Student_ID = e.Student_ID " +
					"LEFT JOIN course c ON c.Course_ID = e.course_ID";
	private static final String SQL_INSERT =
			"INSERT INTO student (StudentId, FirstName, LastName, Address, City, State, ZipCode) VALUES (?, ?, ?, ?, ?, ?, ?)";
	private static final String SQL_UPDATE =
			"UPDATE student SET  StudentId = ?, FirstName = ?, LastName = ?, Address = ?, City = ?, State= ?, ZipCode = ? WHERE Student_ID = ?";
	private static final String SQL_DELETE =
			"DELETE FROM student WHERE Student_ID = ?";
	private static final String SQL_STUDENT_ENROLL =
			"INSERT INTO enrolled_in (Student_ID, Course_ID) VALUES (?, ?)";
	private static final String SQL_STUDENT_UNENROLL =
			"DELETE from enrolled_in WHERE Student_ID = ? AND Course_ID = ?";

	// Vars ---------------------------------------------------------------------------------------

    private DAOFactory daoFactory;

    // Constructors -------------------------------------------------------------------------------

    /**
     * Construct an User DAO for the given DAOFactory. Package private so that it can be constructed
     * inside the DAO package only.
     * @param daoFactory The DAOFactory to construct this User DAO for.
     */
    StudentDAOJDBC(DAOFactory daoFactory) {
        this.daoFactory = daoFactory;
    }

    // Actions ------------------------------------------------------------------------------------

    @Override
    public Student find(Long id) throws DAOException {
        return find(SQL_FIND_BY_ID, id);
    }
    
    @Override
    public Student find(String id) throws DAOException {
        return find(SQL_FIND_BY_SID, id);
    }

    /**
     * Returns the user from the database matching the given SQL query with the given values.
     * @param sql The SQL query to be executed in the database.
     * @param values The PreparedStatement values to be set.
     * @return The user from the database matching the given SQL query with the given values.
     * @throws DAOException If something fails at database level.
     */
    private Student find(String sql, Object... values) throws DAOException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Student student = null;
        Student newStudent = null;

        try {
            connection = daoFactory.getConnection();
            preparedStatement = prepareStatement(connection, sql, false, values);
            resultSet = preparedStatement.executeQuery();
            
            //loop for courses
            while (resultSet.next()) {
                newStudent = map(student, resultSet);
                
                //when new student created, place as current student
                if(newStudent != null)
                	student = newStudent;
            }
        } catch (SQLException e) {
            throw new DAOException(e);
        } finally {
            close(connection, preparedStatement, resultSet);
        }

        return student;
    }

    @Override
    public List<Student> list(int count, int page, boolean getCourses) throws DAOException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Student> students = new ArrayList<Student>();
        Student student = null;
        Student newStudent = null;
        
        int maxReturn = (count > DAOUtil.MAX_ROWS) ? DAOUtil.MAX_ROWS : count;
		int offset = (page > 0) ? page - 1 : 0;
		String limitQuery =  String.format(" LIMIT %d OFFSET %d", maxReturn, maxReturn * offset);

        try {
            connection = daoFactory.getConnection();
            
            if(getCourses) {
            	preparedStatement = prepareStatement(connection, SQL_FIND_ALL_WITH_COURSES + limitQuery, false);
            	resultSet = preparedStatement.executeQuery();
            	//loop for students and courses
            	while (resultSet.next()) {
            		newStudent = map(student, resultSet);

            		//when new student created, place as current student and add to list
            		if(newStudent != null) {
            			student = newStudent;
            			students.add(student);
            		}
            	}
            } else {
            	preparedStatement = prepareStatement(connection, SQL_FIND_ALL + limitQuery, false);
            	resultSet = preparedStatement.executeQuery();
            	while (resultSet.next()) {
            		students.add(map(null, resultSet));
            	}
            }
        } catch (SQLException e) {
            throw new DAOException(e);
        } finally {
            close(connection, preparedStatement, resultSet);
        }

        return students;
    }
    
    @Override
	public List<Student> search(Student searchParams, boolean getCourses) throws DAOException {
    	//prepare
    	StringBuilder query = new StringBuilder(getCourses ? SQL_FIND_BY_GENERIC_WITH_COURSES : SQL_FIND_BY_GENERIC);
    	HashMap<String, Object> queryValues = new HashMap<String, Object>();

    	//build query
    	if(searchParams.getId() != null && searchParams.getId() > 0)
    		queryValues.put(" Student_ID = ?", searchParams.getId());
    	if(!searchParams.getStudentId().isEmpty())
    		queryValues.put(" StudentId = ?", searchParams.getStudentId());
    	if(!searchParams.getFirstName().isEmpty())
    		queryValues.put(" FirstName LIKE ?", wrapLikeCompare(searchParams.getFirstName(), false));
    	if(!searchParams.getLastName().isEmpty())
    		queryValues.put(" LastName LIKE ?", wrapLikeCompare(searchParams.getLastName(), false));
    	if(!searchParams.getAddress().isEmpty())
    		queryValues.put(" Address LIKE ?", wrapLikeCompare(searchParams.getAddress(), true));
    	if(!searchParams.getCity().isEmpty())
    		queryValues.put(" City LIKE ?", wrapLikeCompare(searchParams.getCity(), false));
    	if(!searchParams.getState().isEmpty())
    		queryValues.put(" State = ?", searchParams.getState());
    	if(!searchParams.getZipCode().isEmpty())
    		queryValues.put(" ZipCode LIKE ?", wrapLikeCompare(searchParams.getZipCode(), false));
    	
    	if(queryValues.size() == 0)
    		return list(DAOUtil.MAX_ROWS, 1, getCourses);

    	//transmute
    	Object[] values = new Object[queryValues.size()];
    	query.append(" WHERE");

    	int idx = 0;
    	for(Entry<String, Object> entry : queryValues.entrySet()) {
    		if(idx > 0)
    			query.append(" AND");
    		query.append(entry.getKey());
    		values[idx++] = entry.getValue();
    	}
    	query.append(String.format(" ORDER BY Student_ID LIMIT %d", DAOUtil.MAX_ROWS));

    	//Copy-paste from find(...) and list()
    	Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Student> students = new ArrayList<Student>();
        Student student = null;
        Student newStudent = null;

        try {
            connection = daoFactory.getConnection();

            preparedStatement = prepareStatement(connection, query.toString(), false, values);
            resultSet = preparedStatement.executeQuery();
            //loop for students and courses
            while (resultSet.next()) {
            	newStudent = map(student, resultSet);

            	//when new student created, place as current student and add to list
            	if(newStudent != null) {
            		student = newStudent;
            		students.add(student);
            	}
            }
        } catch (SQLException e) {
        	throw new DAOException(e);
        } finally {
            close(connection, preparedStatement, resultSet);
        }

        return students;
    	//end Copy-past from find(...) and list()
    }

	@Override
	public List<Student> searchStudentsEnrolledIn(Course course) throws DAOException {
		Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Student> students = new ArrayList<Student>();

        try {
        	connection = daoFactory.getConnection();
        	preparedStatement = prepareStatement(connection, SQL_FIND_BY_COURSE, false, course.getId());
        	resultSet = preparedStatement.executeQuery();
        	while (resultSet.next()) {
        		students.add(map(null, resultSet));
        	}
        } catch (SQLException e) {
            throw new DAOException(e);
        } finally {
            close(connection, preparedStatement, resultSet);
        }

        return students;
	}

    @Override
    public void create(Student student) throws IllegalArgumentException, DAOException {
        if (student.getId() != null) {
            throw new IllegalArgumentException("Student is already created, the Student ID is not null.");
        }

        Object[] values = {
        		student.getStudentId(),
        		student.getFirstName(),
        		student.getLastName(),
        		student.getAddress(),
        		student.getCity(),
        		student.getState(),
        		student.getZipCode()
        };

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet generatedKeys = null;

        try {
            connection = daoFactory.getConnection();
            preparedStatement = prepareStatement(connection, SQL_INSERT, true, values);
            int affectedRows = preparedStatement.executeUpdate();
            if (affectedRows == 0) {
                throw new DAOException("Creating student failed, no rows affected.");
            }
            generatedKeys = preparedStatement.getGeneratedKeys();
            if (generatedKeys.next()) {
            	student.setId(generatedKeys.getLong(1));
            } else {
                throw new DAOException("Creating student failed, no generated key obtained.");
            }
        } catch (SQLException e) {
            throw new DAOException(e);
        } finally {
            close(connection, preparedStatement, generatedKeys);
        }
    }
    
    @Override
    public void update(Student student) throws DAOException {
        if (student.getId() == null) {
            throw new IllegalArgumentException("User is not created yet, the user ID is null.");
        }

        Object[] values = {
        		student.getStudentId(),
        		student.getFirstName(),
        		student.getLastName(),
        		student.getAddress(),
        		student.getCity(),
        		student.getState(),
        		student.getZipCode(),
        		student.getId()
        };

        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = daoFactory.getConnection();
            preparedStatement = prepareStatement(connection, SQL_UPDATE, false, values);
            int affectedRows = preparedStatement.executeUpdate();
            if (affectedRows == 0) {
                throw new DAOException("Updating user failed, no rows affected.");
            }
        } catch (SQLException e) {
            throw new DAOException(e);
        } finally {
            close(connection, preparedStatement);
        }
    }
    
    @Override
    public void addCourse(Student s, Course c) throws DAOException {
    	Object[] values = { 
    			s.getId(),
    			c.getId()
    	};

        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = daoFactory.getConnection();
            preparedStatement = prepareStatement(connection, SQL_STUDENT_ENROLL, false, values);
            int affectedRows = preparedStatement.executeUpdate();
            if (affectedRows == 0) {
                throw new DAOException("Enrolling student failed, no rows affected.");
            }
        } catch (SQLException e) {
            throw new DAOException(e);
        } finally {
            close(connection, preparedStatement);
        }
    }
        
    @Override
    public void removeCourse(Student s, Course c) throws IllegalArgumentException, DAOException {
    	Object[] values = { 
    			s.getId(),
    			c.getId()
    	};

        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = daoFactory.getConnection();
            preparedStatement = prepareStatement(connection, SQL_STUDENT_UNENROLL, false, values);
            int affectedRows = preparedStatement.executeUpdate();
            if (affectedRows == 0) {
                throw new DAOException("Unenrolling student failed, no rows affected.");
            }
        } catch (SQLException e) {
            throw new DAOException(e);
        } finally {
            close(connection, preparedStatement);
        }
    }
    
    @Override
    public void delete(Student student) throws DAOException {
        Object[] values = { 
            student.getId()
        };

        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = daoFactory.getConnection();
            preparedStatement = prepareStatement(connection, SQL_DELETE, false, values);
            int affectedRows = preparedStatement.executeUpdate();
            if (affectedRows == 0) {
                throw new DAOException("Deleting student failed, no rows affected.");
            } else {
                student.setId(null);
            }
        } catch (SQLException e) {
            throw new DAOException(e);
        } finally {
            close(connection, preparedStatement);
        }
    }
    
    // Helpers ------------------------------------------------------------------------------------

    /**
     * Map the current row of the given ResultSet to a Student, optionally with course.
     * @param currentStudent 
     * @param resultSet The ResultSet of which the current row is to be mapped to a Student.
     * @return The mapped Student from the current row of the given ResultSet.
     * @throws SQLException If something fails at database level.
     */
    private static Student map(Student currentStudent, ResultSet resultSet) throws SQLException {
    	Long id = resultSet.getLong("Student_ID");
    	boolean isNewStudent = false;
    	
    	//if new set basic student properties
    	if(currentStudent == null || currentStudent.getId() != id) {
    		currentStudent = new Student();
    		currentStudent.setId(id);
    		currentStudent.setStudentId(resultSet.getString("StudentId"));
    		currentStudent.setFirstName(resultSet.getString("FirstName"));
    		currentStudent.setLastName(resultSet.getString("LastName"));
    		currentStudent.setAddress(resultSet.getString("Address"));
    		currentStudent.setCity(resultSet.getString("City"));
    		currentStudent.setState(resultSet.getString("State"));
    		currentStudent.setZipCode(resultSet.getString("ZipCode"));
    		
    		isNewStudent = true;
    	}
    	
    	//add course information (if exists)
    	Long courseId = 0L;
    	try {
    		courseId = resultSet.getLong("Course_ID");
    	} catch(SQLException e) { } //ignore "column doesn't exist" errors
    	if(courseId > 0) {
    		Course c = new Course();
    		c.setId(courseId);
    		c.setCourseName(resultSet.getString("Name"));
    		c.setSection(resultSet.getString("Section"));
    		c.setTimes(resultSet.getString("MeetingTime"));
    		c.setLocation(resultSet.getString("Location"));
    		
    		currentStudent.getEnrolledCourses().add(c);
    	}
    	
    	if(isNewStudent)
    		return currentStudent;
    	return null; //for existing student
    }
}